﻿using System;
using System.Data;
using System.Transactions;
using System.Data.Common;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Skyworth.CRM.Service.Entity;
using Skyworth.CRM.Service.IDAL;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Skyworth.CRM.Common;

namespace Skyworth.CRM.DAL
{
    public class CustActivitiesDAL : SuperDataBase, ICustActivitiesDAL
    {
        public string Add(string strPrefix,CustActivities model)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select @NewCode=case isnull(MAX(FNumber),'1') when '1' then @Prefix + CONVERT(varchar(100),GETDATE(), 112) + '0001'");
                strSql.Append(" else @Prefix + CAST(cast(right(MAX(FNumber),12) as bigint)+1 as varchar) end");
                strSql.Append(" from T_Cust_Activities where LEFT(FNumber,len(@Prefix)+8) = @Prefix + CONVERT(varchar(100),GETDATE(), 112);");
                strSql.Append("insert into T_Cust_Activities(");
                strSql.Append("FNumber,FDate,FBusinessoppId,FMaker,FOppDate,FAddress,FSubmitDate,FNote,PrototypeProgress,OrdereProgress,Feedback,NormalMaintenance)");
                strSql.Append(" values (");
                strSql.Append("@NewCode,@FDate,@FBusinessoppId,@FMaker,@FOppDate,@FAddress,@FSubmitDate,@FNote,@PrototypeProgress,@OrdereProgress,@Feedback,@NormalMaintenance)");
                //strSql.Append(";select @@IDENTITY");                
                DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                //DB.AddInParameter(cmd, "@FNumber", DbType.String, model.FNumber);
                DB.AddInParameter(cmd, "@Prefix", DbType.String, strPrefix);
                DB.AddInParameter(cmd, "@FDate", DbType.DateTime, model.FDate);
                DB.AddInParameter(cmd, "@FBusinessoppId", DbType.Int32, model.FBusinessoppId);
                DB.AddInParameter(cmd, "@FMaker", DbType.Int32, model.FMaker);
                DB.AddInParameter(cmd, "@FOppDate", DbType.DateTime, model.FOppDate);
                DB.AddInParameter(cmd, "@FAddress", DbType.Int32, model.FAddress);
                DB.AddInParameter(cmd, "@FSubmitDate", DbType.DateTime, model.FSubmitDate);
                DB.AddInParameter(cmd, "@FNote", DbType.String, model.FNote);
                DB.AddOutParameter(cmd, "@NewCode", DbType.String, 50);
                DB.AddInParameter(cmd, "@PrototypeProgress", DbType.String, model.PrototypeProgress);
                DB.AddInParameter(cmd, "@OrdereProgress", DbType.String, model.OrdereProgress);
                DB.AddInParameter(cmd, "@Feedback", DbType.String, model.Feedback);
                DB.AddInParameter(cmd, "@NormalMaintenance", DbType.String, model.NormalMaintenance);
                DB.ExecuteNonQuery(cmd);
                string code = DB.GetParameterValue(cmd, "@NewCode").ToString();
                return code;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Update(CustActivities model)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("update T_Cust_Activities set ");
                //strSql.Append("FNumber=@FNumber,");
                strSql.Append("FDate=@FDate,");
                strSql.Append("FBusinessoppId=@FBusinessoppId,");
                strSql.Append("FMaker=@FMaker,");
                strSql.Append("FOppDate=@FOppDate,");
                strSql.Append("FAddress=@FAddress,");
                strSql.Append("FSubmitDate=@FSubmitDate,");
                strSql.Append("FNote=@FNote,");
                strSql.Append("PrototypeProgress=@PrototypeProgress,");
                strSql.Append("OrdereProgress=@OrdereProgress,");
                strSql.Append("Feedback=@Feedback,");
                strSql.Append("NormalMaintenance=@NormalMaintenance ");

                strSql.Append(" where FItemId=@FItemId");
                DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                DB.AddInParameter(cmd, "@FitemId", DbType.Int32, model.FItemId);
                //DB.AddInParameter(cmd, "@FNumber", DbType.String, model.FNumber);
                DB.AddInParameter(cmd, "@FDate", DbType.DateTime, model.FDate);
                DB.AddInParameter(cmd, "@FBusinessoppId", DbType.Int32, model.FBusinessoppId);
                DB.AddInParameter(cmd, "@FMaker", DbType.Int32, model.FMaker);
                DB.AddInParameter(cmd, "@FOppDate", DbType.DateTime, model.FOppDate);
                DB.AddInParameter(cmd, "@FAddress", DbType.Int32, model.FAddress);
                DB.AddInParameter(cmd, "@FSubmitDate", DbType.DateTime, model.FSubmitDate);
                DB.AddInParameter(cmd, "@FNote", DbType.String, model.FNote);
                DB.AddInParameter(cmd, "@PrototypeProgress", DbType.String, model.PrototypeProgress);
                DB.AddInParameter(cmd, "@OrdereProgress", DbType.String, model.OrdereProgress);
                DB.AddInParameter(cmd, "@Feedback", DbType.String, model.Feedback);
                DB.AddInParameter(cmd, "@NormalMaintenance", DbType.String, model.NormalMaintenance);
                DB.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Delete(int FItemId)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("delete from T_Cust_Activities ");
                strSql.Append(" where FItemId=@FItemId");
                DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                DB.AddInParameter(cmd, "@FItemId", DbType.Int32, FItemId);
                DB.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public CustActivities GetModel(int fItemId)
        {
            try
            {
                CustActivities model = null;
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select  top 1 FItemId,FNumber,FDate,FBusinessoppId,FMaker,FOppDate,FAddress,FSubmitDate,FNote,PrototypeProgress,OrdereProgress,Feedback,NormalMaintenance from T_Cust_Activities ");
                strSql.Append(" where FItemId=@FItemId");
                DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                DB.AddInParameter(cmd, "@FItemId", DbType.Int32, fItemId);
                using (IDataReader dr = DB.ExecuteReader(cmd))
                {
                    while (dr.Read())
                    {
                        model = new CustActivities();
                        if (dr["FItemId"].ToString() != "")
                        {
                            model.FItemId = int.Parse(dr["FItemId"].ToString());
                        }
                        model.FNumber = dr["FNumber"].ToString();
                        if (dr["FDate"].ToString() != "")
                        {
                            model.FDate = DateTime.Parse(dr["FDate"].ToString());
                        }
                        if (dr["FBusinessoppId"].ToString() != "")
                        {
                            model.FBusinessoppId = int.Parse(dr["FBusinessoppId"].ToString());
                        }
                        if (dr["FMaker"].ToString() != "")
                        {
                            model.FMaker = int.Parse(dr["FMaker"].ToString());
                        }
                        if (dr["FOppDate"].ToString() != "")
                        {
                            model.FOppDate = DateTime.Parse(dr["FOppDate"].ToString());
                        }
                        if (dr["FAddress"].ToString() != "")
                        {
                            model.FAddress = int.Parse(dr["FAddress"].ToString());
                        }
                        if (dr["FSubmitDate"].ToString() != "")
                        {
                            model.FSubmitDate = DateTime.Parse(dr["FSubmitDate"].ToString());
                        }
                        model.FNote = dr["FNote"].ToString();
                        model.PrototypeProgress = dr["PrototypeProgress"].ToString();
                        model.OrdereProgress = dr["OrdereProgress"].ToString();
                        model.Feedback = dr["Feedback"].ToString();
                        model.NormalMaintenance = dr["NormalMaintenance"].ToString();
                    }
                }
                return model;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable Query(string strWhere)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append(@"select tca.FItemId,tca.FNumber,tca.FDate,FBusinessoppId,tcb.FNumber as BssCode,
tca.FMaker,tbe.EmpName,tca.FAddress,FOppDate,
case tca.FAddress when 1 then '拜访' when 2 then '电话' when 3 then '市场活动' else '传真' end as FType,
FSubmitDate,tca.FNote,tca.PrototypeProgress,tca.OrdereProgress,tca.Feedback,tca.NormalMaintenance FROM T_Cust_Activities as tca
inner join T_Cust_Bussinessopp as tcb on tca.FBusinessoppId = tcb.FItemId
left join T_BasicData_Employee as tbe on tbe.EmpId=tca.FMaker
where 1=1");
                if (strWhere.Trim() != "")
                {
                    strSql.Append(strWhere);
                }
                DataSet ds = DB.ExecuteDataSet(CommandType.Text, strSql.ToString());
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 分页获得数据列表
        /// </summary>
        public DataTable Query(string strWhere, string orderKey, int pageSize, int pageIndex, out int recordCount)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append(@"select tca.FItemId,tca.FNumber,tca.FDate,FBusinessoppId,tcb.FNumber as BssCode,
tca.FMaker,tbe.EmpName,tca.FAddress,FOppDate,
case tca.FAddress when 1 then '拜访' when 2 then '电话' when 3 then '市场活动' else '传真' end as FType,
FSubmitDate,tca.FNote,tca.PrototypeProgress,tca.OrdereProgress,tca.Feedback,tca.NormalMaintenance FROM T_Cust_Activities as tca
inner join T_Cust_Bussinessopp as tcb on tca.FBusinessoppId = tcb.FItemId
left join T_BasicData_Employee as tbe on tbe.EmpId=tca.FMaker
where 1=1");
                if (strWhere.Trim() != "")
                {
                    strSql.Append(strWhere);
                }
                string countsql = "select count(1) from (" + strSql.ToString() + ") as FCount";
                //计算开始索引和结束索引
                int startIndex = pageSize * (pageIndex - 1) + 1;
                int endIndex = startIndex + pageSize - 1;
                //调用分页Sql方法
                string pageSql = Pagination.PageSql(strSql.ToString(), orderKey, startIndex, endIndex);
                //记录总数
                DbCommand cmd = DB.GetSqlStringCommand(countsql);
                recordCount = Convert.ToInt32(DB.ExecuteScalar(cmd));
                cmd = DB.GetSqlStringCommand(pageSql);
                return DB.ExecuteDataSet(cmd).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
